# !/usr/bin/env python
# -*- coding: utf-8 -*-
# @Time    : 2021/4/2 10:12
# @Author  : qiaoc
# @File    : AuditEle.py
# @Desc    :  移动电费核销稽核模块



from ocr.Ocr import *
from dao.PyMysql import *
from affix import AffixUtils
from examine.rule import AuditRule
from examine.po.AuditResult import *
from examine.rule import StampRule
import os

# 不用处理的标杆类型
no_need_to_deal_with_benchmark_type = ['智能电表标杆（动环）', '智能电表标杆（集采）', '动环负载标杆', '平峰谷均价标杆（录平峰谷表采用总量录入方式时）']


"""
 移动电费核销稽核模块 
"""

# 电费类型
def audit_ele(bill_number, floder_path, type):
    """ success == 0 成功通过稽核；\r\n
            success == 1 转人工\r\n
            success == 2 稽核不通过
    """
    AffixUtils.file_classification(floder_path)
    file_flag1 = True  # 判断附件是否存在
    file_flag2 = True
    file_flag3 = True
    file_list1 = []
    file_list2 = []
    file_list3 = []
    stamp1 = []  # 印章识别信息
    stamp2 = []
    stamp3 = []
    rar_file = []
    audit_refused_type = {}  # 审核不通过类型
    auditResult = AuditResult()
    audit_list = []  # 稽核结果集，需存库
    result = {'success': 0, 'message': '通过'}
    try:
        rp_payment_info_tablename = 'rp_payment_info'  # 报账点电费缴费信息表
        rp_payment_info_sql = """SELECT * FROM """ + rp_payment_info_tablename + """ WHERE payment_code='""" + str(
            bill_number) + """' and recycle_id = '0' """
        rp_payment_info_data = PyMysql().get_db_data(rp_payment_info_sql)
        page_business_activities = rp_payment_info_data[0]['business_activities']

        # 判断该工单号是否存在多块电表
        whether_execute = False # 是否执行多块电表
        payment_detail_info_tablename = 'payment_detail_info'  # 电表缴费明细信息-数据库表
        payment_detail_info_sql = """SELECT * FROM """ + payment_detail_info_tablename + """ WHERE payment_code='""" + bill_number + """' and recycle_id = '0' """
        payment_detail_info_data = PyMysql().get_db_data(payment_detail_info_sql)
        # if not payment_detail_info_data or len(payment_detail_info_data) == 0:
        #     auditResult.auditStatus = '审核中'  # 返回稽核结果为转人工
        # es_number = payment_detail_info_data[0]['es_number']  # 页面抓取的电表户号

        execute_list = [] # 户号和对应取值行数
        # 核对数据库是否存在工单数据，以及是否为转供电
        execute_list,auditResult,audit_list = first_audit(page_business_activities, auditResult, audit_list,
                                                            bill_number, whether_execute, payment_detail_info_data,
                                                            execute_list)
        if len(audit_list) == 0:
            # all_file_name = os.listdir(floder_path)
            affix_list_from_db = AffixUtils.get_affix_from_db(bill_number)
            all_file = AffixUtils.get_all_attachment(floder_path)

            """
                判断上传的附件是否存在，是否缺少附件
            """
            audit_list, auditResult= reco_affix_is_exist(all_file, floder_path, auditResult, audit_list,
                                                         bill_number, affix_list_from_db)

            all_ocr_attachment, rar_file = AffixUtils.get_curing_ocr_attachment(floder_path)
            # 第一步：印章识别
            audit_list, auditResult = StampRule.audit_rule_ele_stamp(all_ocr_attachment, rar_file, auditResult, audit_list, bill_number,
                                 file_flag1, affix_list_from_db, file_list1, stamp1, file_flag2,
                                 file_list2, stamp2, file_flag3, file_list3, stamp3)
            # 第二步：查询数据库中的页面信息
            payment_detail_info_tablename = 'payment_detail_info'  # 电表缴费明细信息-数据库表
            payment_detail_info_data = [] # 多块电表缴费明细信息-数据列表
            es_number = ''
            if whether_execute == True:
                for es_number_value in execute_list:
                    payment_detail_info_sql = """SELECT * FROM """ + payment_detail_info_tablename + """ WHERE payment_code='""" + bill_number + """' and es_number='""" + str(es_number_value[0]) + """' and recycle_id = '0' """
                    payment_detail_info_esNumber_data = PyMysql().get_db_data(payment_detail_info_sql)
                    payment_detail_info_data.append(payment_detail_info_esNumber_data[int(es_number_value[1])])
            else:
                payment_detail_info_sql = """SELECT * FROM """ + payment_detail_info_tablename + """ WHERE payment_code='""" + bill_number + """' and recycle_id = '0' """
                payment_detail_info_data = PyMysql().get_db_data(payment_detail_info_sql)
                # if not payment_detail_info_data or len(payment_detail_info_data) == 0:
                #     auditResult.auditStatus = '审核中'  # 返回稽核结果为转人工
                es_number = payment_detail_info_data[0]['es_number']  # 页面抓取的电表户号
            rp_code = rp_payment_info_data[0]['rp_code']  # 报账点编码

            # 第三步：查询数据库已保存的附件OCR信息表中的数据
            att_2_name = all_file['附件2']
            ocr_curing_sql = ''
            if '汇总' in att_2_name:
                if whether_execute == True:
                    ocr_curing_sql = """SELECT * FROM """ + PyMysql().bill_table_name + """ WHERE affixx_name='""" + att_2_name + """'"""
                else:
                    ocr_curing_sql = """SELECT * FROM """ + PyMysql().bill_table_name + """ WHERE uid='""" + es_number + """' and affixx_name='""" + att_2_name + """' """
            else:
                ocr_curing_sql = """SELECT * FROM """ + PyMysql().bill_table_name + """ WHERE bill_number='""" + bill_number + """' """
            bill_table_data = PyMysql().get_db_data(ocr_curing_sql)
            if not bill_table_data and len(bill_table_data) == 0:
                # 找不到，则OCR所有的图片，存库
                # todo : 区分附件进行识别，存储每个附件的识别异常结果
                try:
                    Ocr().ocr_img_list(all_ocr_attachment, bill_number, type, 2)
                except Exception as e:
                    # print(e, ' ', e.__traceback__.tb_lineno)
                    pass

            att_3_name = all_file['附件3']
            rpa_split_ratio_tablename = 'rpa_ocr_split_ratio'  # RPA分割比例记录表
            rpa_split_ratio_sql = """SELECT * FROM """ + rpa_split_ratio_tablename + """ WHERE affixx_name='""" + att_3_name + """'"""
            rpa_split_ratio_data = PyMysql().get_db_data(rpa_split_ratio_sql)
            if not rpa_split_ratio_data or len(rpa_split_ratio_data) == 0:
                # 找不到分摊比例数据则ocr
                try:
                    Ocr().ocr_img_list(all_ocr_attachment, bill_number, type, 3)
                except Exception as e:
                    # print(e, ' ', e.__traceback__.tb_lineno)
                    pass
            rp_agreement_info_tablename = 'rp_agreement_info'  # 报账点与合同信息表
            rp_agreement_info_sql = """SELECT * FROM """ + rp_agreement_info_tablename + """ WHERE rp_code='""" + rp_code + """' and recycle_id = '0' """
            rp_agreement_info_data = PyMysql().get_db_data(rp_agreement_info_sql)
            rp_name = rp_agreement_info_data[0]['rp_name']  # 报账点名称
            rp_name = rp_name.replace(' ','')
            actual_nae_taxsad = rp_payment_info_data[0]['actual_nae_taxsad']  # 实际不含税电费金额
            actual_ef_tariff = rp_payment_info_data[0]['actual_ef_tariff']  # 实际电费税金
            rp_payment_start_time = rp_payment_info_data[0]['start_time']  # 缴费期始
            rp_payment_end_time = rp_payment_info_data[0]['end_time']  # 缴费期终
            rp_payment_info_actual_total_pc = rp_payment_info_data[0]['actual_total_pc']  # 【实际总耗电量】
            payment_electric_info_tablename = 'payment_electric_info'  # 电费缴费金额表
            payment_electric_info_sql = """SELECT * FROM """ + payment_electric_info_tablename + """ WHERE payment_code='""" + bill_number + """' and recycle_id = '0' """
            payment_electric_info_data = PyMysql().get_db_data(payment_electric_info_sql)
            payment_electric_info_actual_reimbursement_amount = payment_electric_info_data[0]['actual_reimbursement_amount']  # 实际报账金额
            actual_reimbursement_amount = payment_electric_info_data[0]['amount_including_tax']  # 【其他费用】的【含税金额】

            # 页面【缴费标杆信息】-【标杆类型】
            payment_benchmark_info_tablename = 'payment_benchmark_info'  # 缴费标杆信息
            payment_benchmark_info_sql = """SELECT * FROM """ + payment_benchmark_info_tablename + """ WHERE payment_code='""" + bill_number + """' and recycle_id = '0' """
            payment_benchmark_info_data = PyMysql().get_db_data(payment_benchmark_info_sql)  # 结果会查到多个标杆信息，需要循环遍历判断
            current_share_ratio = payment_detail_info_data[0]['current_share_ratio']  # 页面【电费缴费明细信息】-【本次实际分摊比例】
            current_share_ratio = re.findall(r"\d+\.?\d*", str(current_share_ratio).replace('%', ''))[0]
            current_reading = payment_detail_info_data[0]['current_reading']  # 页面【电表缴费明细信息】-【本期读数（度）】
            last_reading = payment_detail_info_data[0]['last_reading']  # 页面【电表缴费明细信息】- 【上期读数（度）】
            es_magnification = payment_detail_info_data[0]['es_magnification']  # 电表倍率

            select_page_ele_info_data = PyMysql().select_eleinfo(payment_detail_info_tablename, 'payment_code',
                                                       str(bill_number))
            electricity_price = ''
            electricity_consumption = ''
            electricity_loss = ''
            if whether_execute == False:
                if select_page_ele_info_data and len(select_page_ele_info_data):
                    electricity_price = payment_detail_info_data[0]['electricity_price']  # 页面【电表缴费明细信息】-【电费单价（元/度）】
                    electricity_consumption = payment_detail_info_data[0]['electricity_consumption']  # 页面【电表缴费明细信息】-【用电量（度）】 '用电量1：2374.26791'
                    select_data_list = PyMysql().select_eleinfo('payment_detail_info', 'payment_code', bill_number)
                    electricity_consumption_list = select_data_list[1] # 页面【电表缴费明细信息】-【用电量（度）】 拆分成list循环对比
                    electricity_loss = payment_detail_info_data[0]['electricity_loss']  # 页面【电表缴费明细信息】-【电损电量】

            share_degrees = payment_detail_info_data[0]['share_degrees']  # 【分摊后度数(度)】

            """
                综合稽核 - 页面稽核
            """
            audit_list, auditResult, audit_refused_type = AuditRule.audit_rule_ele_page(auditResult, audit_list, bill_number, result, audit_refused_type,
                                payment_electric_info_actual_reimbursement_amount, rp_payment_info_actual_total_pc,
                                actual_nae_taxsad, actual_ef_tariff, actual_reimbursement_amount, whether_execute,
                                payment_detail_info_data, electricity_loss, share_degrees,
                                payment_benchmark_info_data, all_file, affix_list_from_db, floder_path, es_number)
            # 第四步：查询数据库中的OCR数据
            bill_table_data = PyMysql().get_db_data(ocr_curing_sql)
            if not bill_table_data and len(bill_table_data) == 0:
                """
                    数据库没有对应得附件识别数据，稽核处理如下：
                """
                audit_list, auditResult,audit_refused_type = AuditRule.audit_rule_ele_no_data(auditResult, audit_list, rar_file, bill_number, affix_list_from_db,
                                       whether_execute, audit_refused_type,
                                       rp_name, payment_electric_info_actual_reimbursement_amount, current_reading,
                                       electricity_consumption,
                                       payment_detail_info_data, rp_payment_start_time, rp_payment_end_time,
                                       electricity_price, electricity_loss,
                                       current_share_ratio, file_flag2, file_flag3, all_file, execute_list, es_number)
            else:
                bill_table_uname = bill_table_data[0]['uname']  # 附件二【用户编号】的【客户地址】
                bill_table_uname = bill_table_uname.replace(' ', '')
                if not bill_table_uname:
                    bill_table_uname = ''
                bill_table_ele_charge = bill_table_data[0]['ele_charge']  # 附件2（以户号为依据）的金额
                bill_table_last_display_num = bill_table_data[0]['last_display_num']  # 附件2上期读数
                bill_table_now_display_num = bill_table_data[0]['now_display_num']  # 附件2 - 本期读数
                bill_table_total_power = bill_table_data[0]['total_power']  # 总电量
                bill_table_start_date = bill_table_data[0]['start_date']  # 读数开始日期
                bill_table_end_date = bill_table_data[0]['end_date']  # 读数结束日期
                # ocrd_electricity_consumption = bill_table_data[0]['electricity_consumption']  # OCR总电量
                ocr_electricity_loss = bill_table_data[0]['ele_loss']  # OCR识别保存的电损
                bill_table_data_electricity_price = str(bill_table_data[0]['ele_price']).strip()  # 电价
                now_display_num = bill_table_data[0]['now_display_num']  # 【本期指示】/【本月】/本次抄见示数

                bill_table_data_list = [] # 多块电表，户号对应的识别结果 结构[[户号1，识别结果]，[户号2，识别结果]]
                for data in payment_detail_info_data:
                    ocr_curing_sql = ''
                    if '汇总' in att_2_name:
                        ocr_curing_sql = """SELECT * FROM """ + PyMysql().bill_table_name + """ WHERE uid='""" + data['es_number'] + """' and affixx_name='""" + att_2_name + """' """
                    else:
                        ocr_curing_sql = """SELECT * FROM """ + PyMysql().bill_table_name + """ WHERE bill_number='""" + bill_number + """' """
                    ocr_table_data = PyMysql().get_db_data(ocr_curing_sql)
                    if not ocr_table_data and len(ocr_table_data) == 0:
                        bill_table_data_list.append([data['es_number'],''])
                    else:
                        bill_table_data_list.append([data['es_number'],ocr_table_data[0]])
                """
                    综合稽核 - 数据稽核
                """
                audit_list, auditResult,audit_refused_type = AuditRule.audit_rule_ele_data(audit_list, auditResult, bill_number, rar_file, whether_execute,
                                  audit_refused_type,
                                  result,
                                  file_flag2, payment_electric_info_actual_reimbursement_amount,
                                  affix_list_from_db,
                                  payment_detail_info_data, bill_table_ele_charge, current_share_ratio, rp_name,
                                  bill_table_uname,
                                  all_file, file_flag3, execute_list, es_number,
                                  bill_table_data_electricity_price,
                                  current_reading,
                                  bill_table_data, electricity_price, bill_table_data_list,
                                  rp_payment_start_time,
                                  rp_payment_end_time,
                                  bill_table_start_date, bill_table_end_date, electricity_loss,
                                  ocr_electricity_loss,
                                  electricity_consumption, bill_table_now_display_num,
                                  electricity_consumption_list, bill_table_last_display_num, now_display_num,bill_table_total_power,
                                  last_reading, es_magnification,page_business_activities)
        # 存储稽核信息到数据库中
        PyMysql.insert_data(PyMysql().nf_audit_point_tablename, PyMysql().nf_audit_point_table_key, audit_list)

        # 更新工单审核信息
        subAuditStatus = getSubAuditStatus(audit_list)
        is_artificial = 1  # 是否转人工
        if subAuditStatus == '审核中':
            is_artificial = 0
        elif subAuditStatus == '不通过':
            # 不通过类型插入数据库动作
            PyMysql().ergodic_Noresult_audit_list(audit_list)
        if subAuditStatus == '通过':
            PyMysql().update_nf_audit_info(str(bill_number), subAuditStatus, '')
        audit_refused_type_str = ''
        for t in audit_refused_type:
            if t:
                audit_refused_type_str = audit_refused_type_str + ',' + str(t)
            else:
                audit_refused_type_str = str(t)
        audit_remark = ''  # 审核意见
        for po in audit_list:
            if audit_remark:
                if "合格" in str(po[6]):
                    continue
                audit_remark = audit_remark + ',' + str(po[6])
            else:
                if "合格" in str(po[6]):
                    continue
                audit_remark = str(po[6])
        auditResult.message = audit_remark
        type_ele = '报账点电费审核'
        save_time = time.strftime("%Y-%m-%d %H:%M:%S", time.localtime())
        update_sql = """UPDATE """ + PyMysql().sys_audit_info_tablename + """ SET audit_status='""" + \
                     str(subAuditStatus) + """',audit_refused_type='""" + str(
            audit_refused_type_str) + """',audit_rd_type='""" + str(audit_remark) + """',is_artificial='""" + \
                     str(is_artificial) + """',save_time='""" + str(
            save_time) + """' WHERE payment_code='""" + str(
            bill_number) + """' AND document_type='""" + str(type_ele) + """'"""
        PyMysql().execute_sql(update_sql)

        rpv_update_sql = """ update rp_payment_info SET audit_status='""" + str(
            subAuditStatus) + """' where payment_code = '""" + str(bill_number) + """' """
        PyMysql().execute_sql(rpv_update_sql)
    except Exception as e:
        import traceback
        traceback.print_exc()
        print(e,e.__traceback__.tb_lineno)
        pass
        result['success'] = 1
        result['message'] = 'RPA处理异常'
    return result

def getSubAuditStatus(audit_list):
    no_audit_list = ['总金额（其他费用）', '总金额（核销/支付）', '实际总金额', '缴费期', '分摊比例', '本期读数', '电费单价', '上期读数', '用电量', '电损电量']
    artificial_list = ['平均电费单价', '电损电量占比', '超标说明']
    audit_status_list = []
    for auditInfo in audit_list:
        if auditInfo[1] in no_audit_list and auditInfo[4] == '0':
            audit_status_list.append('不通过')
        if auditInfo[1] in artificial_list and auditInfo[4] == '0':
            audit_status_list.append('审核中')

    if audit_list:
        if '不通过' in audit_status_list:
            return '不通过'
        if '审核中' in audit_status_list:
            return '审核中'
    return '通过'

def first_audit(page_business_activities,auditResult,audit_list,
                    bill_number,whether_execute,payment_detail_info_data,execute_list):
    """
     1. 查询是否存在电表信息
     2. 判断是否存在多块电表，如果是多块电表，是否存在相同户号
     3. 判断缴费单业务活动是否为：转供电
    :param page_business_activities:
    :param auditResult:
    :param audit_list:
    :param bill_number:
    :param whether_execute:
    :param payment_detail_info_data:
    :param execute_list:
    :return:
    """

    if len(payment_detail_info_data) > 1:
        es_number_list = []  # 户号列表（该单据所有户号包含重复户号）
        es_number_value_list = []  # 户号内容列表（该单据所有户号包含重复户号）
        for content in payment_detail_info_data:
            es_number_list.append(content['es_number'])
            es_number_value_list.append(
                [content['es_number'], content['electricity_consumption'], content['electricity_loss']])
        # 判断es_number_list是否有相同户号
        set_es_number_list = set(es_number_list)
        if len(es_number_list) == len(set_es_number_list):
            # 　多块电表不同户号场景
            whether_execute = True
        else:
            execute_list = PyMysql().select_scene('payment_detail_info', 'payment_code', bill_number,
                                                  set_es_number_list)
            if len(execute_list) == len(set_es_number_list):
                whether_execute = True
            else:
                print('多块电表存在相同户号且多行数据用电量和电损电量不为0')

    if page_business_activities and '转供电' in page_business_activities:
        auditResult.auditStatus = '审核中'
        audit_list.append([str(bill_number), '供电类型判断', page_business_activities, '', '0', 'RPA',
                           '页面【报账点缴费信息】-【业务活动】存在“转供电”直接转人工不作稽核', '', '1', ''])
    elif whether_execute == False and len(payment_detail_info_data) > 1:
        auditResult.auditStatus = '审核中'
        audit_list.append([str(bill_number), '电表类型判断', page_business_activities, '', '0', 'RPA',
                           '多块电表存在相同户号含多行数据用电量和电损电量不为0', '', '1', ''])
    elif len(payment_detail_info_data) == 0:
        auditResult.auditStatus = '审核中'
        audit_list.append([str(bill_number), '电表类型判断', page_business_activities, '', '0', 'RPA',
                           '【电表缴费明细信息】为空', '', '1', ''])
    return execute_list,auditResult,audit_list


"""
    判断需要识别的附件是否存在
"""
def reco_affix_is_exist(all_file,floder_path,auditResult,audit_list,bill_number,affix_list_from_db):

    a_1, a_2, a_3, a_4, a_5, a_6 = 0, 0, 0, 0, 0, 0
    for f in all_file:
        if '附件1' in all_file[f]:
            a_1 = 1
        if '附件2' in all_file[f]:
            a_2 = 1
        if '附件3' in all_file[f]:
            a_3 = 1
        if '附件4' in all_file[f]:
            a_4 = 1
        if '附件5' in all_file[f]:
            a_5 = 1
        if '附件6' in all_file[f] and 'xlsx' in all_file[f]:
            a_6 = 1
            table_sql = """SELECT * FROM rpa_standard_site WHERE affixx_name='""" + all_file[f] + """'"""
            standard_table_data = PyMysql().get_db_data(table_sql)
            if not standard_table_data and len(standard_table_data) == 0:
                path = floder_path + '//附件6//' + all_file[f]
                AffixUtils.excel_to_data(path, all_file[f])
    if a_1 == 0 or a_2 == 0 or a_3 == 0:
        auditResult.auditStatus = '审核中'  # 转人工
        if a_1 == 0:
            file_flag1 = False
            auditResult.auditStatus = '审核中'  # 返回稽核结果为转人工
            audit_list.append([str(bill_number), '发票印章', '“电力”或“供电”', '', '0', 'RPA', '附件1发票不存在',
                               AffixUtils.get_affix_url_with_name(affix_list_from_db, '附件1'), '5', ''])
        if a_2 == 0:
            file_flag2 = False
            auditResult.auditStatus = '审核中'  # 返回稽核结果为转人工
            audit_list.append([str(bill_number), '电力明细印章', '“电力”或“供电”', '', '0', 'RPA', '附件2电力明细清单不存在',
                               AffixUtils.get_affix_url_with_name(affix_list_from_db, '附件2'), '5', ''])
        elif a_3 == 0:
            file_flag3 = False
            auditResult.auditStatus = '审核中'  # 返回稽核结果为转人工
            audit_list.append([str(bill_number), '分摊比例印章', '“铁塔”“移动”“联通”“电信”', '', '0', 'RPA', '附件3分摊比例不存在',
                               AffixUtils.get_affix_url_with_name(affix_list_from_db, '附件3'), '5', ''])
    return audit_list,auditResult